﻿'Description:废品单信息记录类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:YangJiang
'create Date:2018-3-19
'Rewriter:
'Rewrite Date:

Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient

Public Class uLMSScrapInfoBusiness
    Implements uLMSScrapInfoIBusiness

    ''' <summary>
    ''' 获取已审核废品单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetScrapInfoCheck(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSScrapInfoIBusiness.GetScrapInfoCheck
        Dim dt As New DataTable
        Dim strsql As New StringBuilder
        'update:wangjh 20180903 em.fullname Employee->em.fullname ZBFR
        strsql.AppendLine("SELECT em.fullname ZBFR,csi.checkdate,csi.zjyy,csi.zjyszr,csi.zzrz,csi.zzrdw,csi.zclph1,e.fullname CheckEmployee,csi.createdate,csi.zfpsl,csi.zfpzl,")
        strsql.AppendLine("csi.zxmno1,csi.zxmno2,csi.zxmno3,csi.zxmno4,csi.zxmno5,csi.zxmno6,csi.zxmno7,csi.zxmno8,csi.zgamng1,csi.zgamng2,csi.zgamng3,csi.zgamng4,csi.zgamng5,csi.zgamng6,csi.zgamng7,csi.zgamng8,")
        strsql.AppendLine("csi.zzfplx,csi.zbfljlx,csi.lossqty,csi.zlph,csi.zpch,csi.zywlh,csi.zywlpc1,csi.zfpdbh,csi.qmnum,csi.zgd,csi.fertvornr,csi.zmesgxtxt,nvl(csi.uvorn,csi.fertvornr) uvorn,")
        strsql.AppendLine("nvl(csi.zltxa1,csi.zmesgxtxt) zltxa1,csi.posnr,csi.zzfpply,csi.zjx,csi.qmgrp,csi.qmcod,csi.fegrp,csi.fecod,csi.matnr,csi.fertaufnr,csi.zdw,csi.zzttxt,")
        strsql.AppendLine("csi.fetxt,csi.zqxtxt,csi.zbz,csi.containerstepscrapinfoid,csi.employeeid,csi.checkemployeeid,csi.workflowstepid,csi.containerid,co.productname,co.productdescription")
        strsql.AppendLine(",co.workflowid,co.containername,NVL(CO.NONCONFORMINGNUM,0) NONCONFORMINGNUM,CO.QTY,CO.WORKFLOWSTEPSEQUENCE SEQUENCE,DECODE(CO.CONTAINERTYPE,2,'批量加工',3,'批量带子序号',7,'单件加工') AS CONTAINERTYPENAME")
        strsql.AppendLine("FROM containerstepscrapinfo csi")
        strsql.AppendLine("LEFT JOIN employee em ON csi.employeeid=em.employeeid LEFT JOIN employee e ON csi.checkemployeeid=e.employeeid")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CO ON CO.CONTAINERID = Csi.CONTAINERID ")
        strsql.AppendLine("WHERE CSI.ISUSE=0")
        strsql.AppendFormat(" AND CSI.FACTORYID='{0}'", para("FactoryID"))
        If para.Keys.Contains("ContainerID") Then
            strsql.AppendFormat(" AND csi.containerid = '{0}'", para("ContainerID"))
        End If
        If para.Keys.Contains("ContainerStepScrapInfoID") Then
            strsql.AppendFormat(" AND csi.ContainerStepScrapInfoID = '{0}'", para("ContainerStepScrapInfoID"))
        End If
        If para.Keys.Contains("ProductName") Then
            strsql.AppendFormat(" AND csi.MATNR LIKE '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("ContainerName") Then
            strsql.AppendFormat(" AND co.containername LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("mfgOrderName") Then
            strsql.AppendFormat(" AND CO.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.Keys.Contains("WorkflowStepName") Then
            strsql.AppendFormat(" AND csi.FERTVORNR LIKE '%{0}%'", para("WorkflowStepName"))
        End If
        If para.Keys.Contains("BeginDate") Then
            strsql.AppendFormat(" AND csi.CREATEDATE >TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("BeginDate")))
        End If
        If para.Keys.Contains("EndDate") Then
            strsql.AppendFormat(" AND csi.CREATEDATE <TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("EndDate")))
        End If

        'strsql.AppendLine(" UNION ")


        'strsql.Insert(0, "select * from (")
        'strsql.AppendLine(" ) ")
        strsql.AppendLine("ORDER BY checkdate DESC") 'add YangJiang 20180725 按审核时间排序
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)

        Return result
    End Function


    ''' <summary>
    ''' 获取未审核废品单信息 Modify by YangJiang 20180611 c.familylistnames
    ''' 添加报废资料的取值来源CHECKQTYCHANGERECORD Modify by Yuxj20181024
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetScrapInfoUnchecked(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSScrapInfoIBusiness.GetScrapInfoUnchecked
        Dim dt As New DataTable
        Dim strsql As New StringBuilder
        Dim strsqlPrefix As String =
            <string>select  CASE WHEN cm.containerid IS NULL THEN em.fullname WHEN cm.containerid IS NOT NULL THEN '' END AS employee,e.fullname zjyy,pb.productname matnr,abs(cc.qty) lossqty,
s.specno uvorn,s.parentspecno fertvornr,uo.uomname zdw,REPLACE(c.containername, pb.productname || '/', '') zpch,p.materialtrademark zclph1,u.attributevalue zlph,
s.opdesc zltxa1,m.mfgordername fertaufnr,ct.familylistids zjx,ct.familylistnames zjx1,c.containerid,wfs.workflowstepid,cc.createdate,cc.checkqtychangerecordid qtyhistorydetailsid,
pb.productname,p.description productdescription,nvl(csi.zfpsl, 0) zfpsl,(abs(cc.qty) - nvl(csi.zfpsl, 0)) qty,cm.containerid waiwei,nvl(wc.workcentername, ct.fromworkcentername) zgd,
decode(cm.containerid, NULL, e.fullname, eem.fullname) zbfr,decode(cm.containerid, NULL, e.employeeid, eem.employeeid) employeeid,
decode(c.containertype,2, '批量加工',3,'批量带子序号',7,'单件加工') AS containertypename   FROM checkqtychangerecord cc
inner JOIN container c ON cc.containerid = c.containerid
inner JOIN containercurrentinfo ct ON  ct.containerid=nvl(c.parentcontainerid,cc.containerid )
LEFT JOIN containercheckinfo gx ON cc.containercheckinfoid = gx.containercheckinfoid AND cc.scrapsourcetype = '1' 
LEFT JOIN containerfinishedcheckinfo cp ON cc.containercheckinfoid = cp.containerfinishedcheckinfoid AND cc.scrapsourcetype = '2'
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = cc.containerstepworkreportid and csw.isuse = 0
LEFT JOIN (SELECT css.qtyhistorydetailsid, SUM(css.zfpsl) zfpsl FROM containerstepscrapinfo css
   GROUP BY css.qtyhistorydetailsid) csi
ON cc.checkqtychangerecordid = csi.qtyhistorydetailsid
LEFT JOIN workflowstep wfs ON cc.workflowstepid = wfs.workflowstepid
LEFT JOIN containercommissioning cm ON cm.containerid = ct.containerid AND cm.workflowstepid = cc.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid
LEFT JOIN spec s ON s.specid = nvl(sb.revofrcdid, wfs.specid)
LEFT JOIN uom uo ON uo.uomid = c.uomid
LEFT JOIN containerproductfamilylist cpl ON cpl.containerid = ct.containerid
LEFT JOIN product p ON p.productid = c.productid
LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid
inner JOIN mfgorder m ON c.mfgorderid = m.mfgorderid
LEFT JOIN userattribute u ON c.containerid = u.parentid AND u.userattributename = '熔炉号母'
LEFT JOIN employee em ON csw.workerid = em.employeeid
LEFT JOIN employee e ON cc.employeeid = e.employeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = nvl(em.employeeid, e.employeeid)
LEFT JOIN workcenter wc ON wc.workcenterid = csw.workcenterid
LEFT JOIN employee eem ON eem.employeeid = cm.outemployeeid --modify tianFW
WHERE cc.changeqtytype = '2' and cc.isuse=0 and (gx.isuse=0 or cp.isuse=0) </string>.Value
        strsql.AppendLine(strsqlPrefix)
        strsql.AppendFormat(" AND (csi.QtyHistoryDetailsID IS  NULL OR csi.zfpsl< ABS(cc.qty)) AND sv.factoryid='{0}'", para("FactoryID"))
         If para.Keys.Contains("ContainerID") Then
            strsql.AppendFormat(" AND cc.containerid = '{0}'", para("ContainerID"))
        End If
        If para.Keys.Contains("ProductName") Then
            strsql.AppendFormat(" AND ct.productname LIKE '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("ContainerName") Then
            strsql.AppendFormat(" AND ct.containername LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("mfgOrderName") Then
            strsql.AppendFormat(" AND ct.mfgordername LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.Keys.Contains("WorkflowStepName") Then
            strsql.AppendFormat(" AND wfs.WorkflowStepName LIKE '%{0}%'", para("WorkflowStepName"))
        End If
        If para.Keys.Contains("BeginDate") Then
            strsql.AppendFormat(" AND cc.CREATEDATE >TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("BeginDate")))
        End If
        If para.Keys.Contains("EndDate") Then
            strsql.AppendFormat(" AND cc.CREATEDATE <TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("EndDate")))
        End If
        Dim strsqlLast As String =
            <string> GROUP BY (EM.FULLNAME, E.FULLNAME, PB.PRODUCTNAME, ABS(cc.QTY), S.SPECNO,
S.PARENTSPECNO, CSI.ZFPSL, C.CONTAINERNAME, P.MATERIALTRADEMARK,
U.ATTRIBUTEVALUE, S.OPDESC, M.MFGORDERNAME, C.CONTAINERID,
EM.EMPLOYEEID, WFS.WORKFLOWSTEPID, cc.createdate, C.MFGORDERID,
U.PARENTID, PB.PRODUCTNAME, P.DESCRIPTION, ct.FAMILYLISTIDS,
ct.FAMILYLISTNAMES, cc.checkqtychangerecordid, CM.CONTAINERID,
UO.UOMNAME, NVL(WC.WORKCENTERNAME, ct.FROMWORKCENTERNAME),
DECODE(CM.CONTAINERID, NULL, E.FULLNAME, EEM.FULLNAME),
DECODE(CM.CONTAINERID, NULL, E.EMPLOYEEID, EEM.EMPLOYEEID), CASE
WHEN CM.CONTAINERID IS NULL THEN
EM.FULLNAME
WHEN CM.CONTAINERID IS NOT NULL THEN
''
END, (ABS(cc.QTY) - NVL(CSI.ZFPSL, 0))),
C.CONTAINERTYPE </string>

        strsql.AppendLine(strsqlLast)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)

        Return result
    End Function
    'Public Function GetScrapInfoUnchecked(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSScrapInfoIBusiness.GetScrapInfoUnchecked
    '    Dim dt As New DataTable
    '    Dim strsql As New StringBuilder

    '    strsql.AppendLine(" SELECT  CASE WHEN cm.containerid IS NULL THEN em.fullname  WHEN cm.containerid IS NOT NULL THEN '' END AS Employee,e.fullname ZJYY, pb.productname MATNR,abs(q.qty) LossQty,s.specno UVORN,s.parentspecno FERTVORNR,uo.uomname ZDW,")
    '    strsql.AppendLine(" REPLACE(c.containername,pb.productname||'/','') ZPCH,p.materialtrademark ZCLPH1,u.attributevalue ZLPH,s.opdesc ZLTXA1,m.mfgordername FERTAUFNR, c.familylistids ZJX,c.familylistnames ZJX1,")
    '    strsql.AppendLine("c.containerid,wfs.workflowstepid,h.txndate CreateDate,qd.qtyhistorydetailsid,pb.productname,p.description productdescription,nvl(csi.zfpsl,0)zfpsl,(abs(q.qty)-nvl(csi.zfpsl,0)) Qty,")
    '    strsql.AppendLine("cm.containerid WAIWEI,nvl(wc.workcentername,c.fromworkcentername) ZGD,DECODE(cm.containerid,NULL,e.fullname,eem.fullname) ZBFR,DECODE(cm.containerid,NULL,e.employeeid,eem.employeeid) employeeid,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME")
    '    strsql.AppendLine(" FROM qtyhistory q LEFT JOIN qtyhistorydetails qd ON q.qtyhistoryid=qd.qtyhistoryid")
    '    strsql.AppendLine(" LEFT JOIN ( SELECT css.qtyhistorydetailsid,SUM(css.zfpsl) zfpsl FROM containerstepscrapinfo css GROUP BY css.qtyhistorydetailsid) csi ON qd.qtyhistorydetailsid = csi.qtyhistorydetailsid") 'Modify YangJiang 20180801
    '    strsql.AppendLine(" LEFT JOIN historymainline h ON q.historymainlineid=h.historymainlineid")
    '    strsql.AppendLine(" LEFT JOIN Workflowstep wfs ON h.workflowstepid=wfs.workflowstepid")

    '    'strsql.AppendLine(" LEFT JOIN subworkflowcheckinfo sc ON sc.subworkflowid=wfs.workflowid AND sc.containerid=q.historyid")
    '    'strsql.AppendLine(" LEFT JOIN Workflowstep wfs1 ON wfs1.workflowstepid=sc.workflowstepid")
    '    'strsql.AppendLine(" LEFT JOIN specbase sb1 ON sb1.specbaseid=wfs1.specbaseid")
    '    'strsql.AppendLine(" LEFT JOIN spec s1 ON s1.specid=NVL(sb1.revofrcdid,wfs1.specid)")

    '    strsql.AppendLine(" LEFT JOIN containercommissioning cm ON cm.containerid=q.historyid")
    '    strsql.AppendLine(" LEFT JOIN uom uo ON uo.uomid=qd.uomid")

    '    strsql.AppendLine(" LEFT JOIN specbase sb ON sb.specbaseid=wfs.specbaseid")
    '    strsql.AppendLine(" LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,wfs.specid)")

    '    strsql.AppendLine(" LEFT JOIN containercurrentinfo c ON q.historyid=c.containerid")
    '    strsql.AppendLine(" LEFT JOIN containerproductfamilylist cpl ON cpl.containerid=c.containerid")
    '    strsql.AppendLine(" LEFT JOIN product p ON p.productid=c.productid")
    '    strsql.AppendLine(" LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid")
    '    strsql.AppendLine(" LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid")
    '    strsql.AppendLine(" LEFT JOIN userattribute u ON c.containerid=u.parentid AND u.userattributename='熔炉号母'")
    '    strsql.AppendLine(" LEFT JOIN employee em ON qd.workerid=em.employeeid")
    '    strsql.AppendLine(" LEFT JOIN employee e ON qd.checkerid=e.employeeid")
    '    strsql.AppendLine(" LEFT JOIN sessionvalues sv ON sv.employeeid=NVL(em.employeeid,e.employeeid)")
    '    'add 'and csw.isuse = 0 ' by tianFW 2018年9月29日
    '    strsql.AppendLine(" LEFT JOIN ContainerStepWorkReport csw ON CSw.Containerid=q.historyid and csw.workflowstepid=h.workflowstepid and csw.isuse = 0") 'Modify YangJiang 20180723 and csw.workflowstepid=h.workflowstepid
    '    strsql.AppendLine(" LEFT JOIN workcenter wc ON wc.workcenterid=csw.workcenterid")
    '    strsql.AppendLine(" LEFT JOIN employee eem ON eem.employeeid=cm.inemployeeid")
    '    strsql.AppendFormat(" WHERE q.qty<0 AND qd.checkerid IS NOT NULL AND (csi.qtyhistorydetailsid IS  NULL OR csi.zfpsl< ABS(q.qty)) AND sv.factoryid='{0}'", para("FactoryID"))
    '    strsql.AppendLine(" AND c.containername IS NOT NULL AND m.mfgordername IS NOT NULL AND qd.changeqtytype='2'")
    '    If para.Keys.Contains("ContainerID") Then
    '        strsql.AppendFormat(" AND c.containerid = '{0}'", para("ContainerID"))
    '    End If
    '    If para.Keys.Contains("ProductName") Then
    '        strsql.AppendFormat(" AND pb.productname LIKE '%{0}%'", para("ProductName"))
    '    End If
    '    If para.Keys.Contains("ContainerName") Then
    '        strsql.AppendFormat(" AND c.containername LIKE '%{0}%'", para("ContainerName"))
    '    End If
    '    If para.Keys.Contains("mfgOrderName") Then
    '        strsql.AppendFormat(" AND m.mfgordername LIKE '%{0}%'", para("mfgOrderName"))
    '    End If
    '    If para.Keys.Contains("WorkflowStepName") Then
    '        strsql.AppendFormat(" AND s.parentspecno LIKE '%{0}%'", para("WorkflowStepName"))
    '    End If
    '    If para.Keys.Contains("BeginDate") Then
    '        strsql.AppendFormat(" AND qd.checkdate >TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("BeginDate")))
    '    End If
    '    If para.Keys.Contains("EndDate") Then
    '        strsql.AppendFormat(" AND qd.checkdate <TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("EndDate")))
    '    End If
    '    strsql.AppendLine("GROUP BY (em.fullname ,e.fullname , pb.productname ,abs(q.qty) ,s.specno ,s.parentspecno ,csi.zfpsl, c.containername ,p.materialtrademark ,")
    '    strsql.AppendLine("u.attributevalue ,s.opdesc ,m.mfgordername , c.containerid ,em.employeeid ,wfs.workflowstepid ,h.txndate ,c.mfgorderid,u.parentid,")
    '    strsql.AppendLine("pb.productname,p.description,c.familylistids,c.familylistnames,qd.qtyhistorydetailsid,cm.containerid,uo.uomname,nvl(wc.workcentername,c.fromworkcentername)")
    '    strsql.AppendLine(",DECODE(cm.containerid,NULL,e.fullname,eem.fullname),DECODE(cm.containerid,NULL,e.employeeid,eem.employeeid),")
    '    strsql.AppendLine("CASE WHEN cm.containerid IS NULL THEN em.fullname   WHEN cm.containerid IS NOT NULL THEN '' END,(abs(q.qty)-nvl(csi.zfpsl,0))),C.CONTAINERTYPE")
    '    Dim paraPage As New uMESPagingDataDTO
    '    paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
    '    paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
    '    paraPage.strSQL = strsql.ToString
    '    Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)

    '    Return result
    'End Function

    ''' <summary>
    ''' 插入数据到废品通知单信息记录表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsiteScrapData(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSScrapInfoIBusiness.InsiteScrapData
        Dim strsql As New StringBuilder
        strsql.AppendLine("INSERT INTO containerstepscrapinfo(QMNUM,MATNR,FERTAUFNR,FERTVORNR,ZMESGXTXT,  UVORN,ZLTXA1,ZZFPLX,FactoryID, " &
                       " ZBFLJLX ,ZZFPPLY,ZFPDBH,ZJX,ZPCH,ZLPH,ZZRDW,ZZRZ,ZJYY,ZJYSZR,FETXT,ZQXTXT,EmployeeID," &
                       " ZGD,ZFPSL,ZFPZL,ZDW, ZYWLH,ZCLPH1,ZYWLPC1,ZBZ,QMGRP,QMCOD,ZZTTXT,POSNR,FEGRP,FECOD," &
                      " ContainerID,WorkflowstepID,LossQty,CreateDate,CheckEmployeeID,CheckDate,IsUse,IsIntegration,IsTransfer,ContainerStepScrapInfoID,QtyHistoryDetailsID," &
                      " ZXMNO1,ZXMNO2,ZXMNO3,ZXMNO4,ZXMNO5,ZXMNO6,ZXMNO7,ZXMNO8,ZGAMNG1,ZGAMNG2,ZGAMNG3,ZGAMNG4,ZGAMNG5,ZGAMNG6,ZGAMNG7,ZGAMNG8)")
        strsql.AppendLine("values (:QMNUM,:MATNR,:FERTAUFNR,:FERTVORNR,:ZMESGXTXT,:UVORN,:ZLTXA1,:ZZFPLX,:FactoryID, " &
                       " :ZBFLJLX,:ZZFPPLY,:ZFPDBH,:ZJX,:ZPCH,:ZLPH,:ZZRDW,:ZZRZ,:ZJYY,:ZJYSZR,:FETXT,:ZQXTXT,:EmployeeID," &
                       " :ZGD,:ZFPSL,:ZFPZL,:ZDW,:ZYWLH,:ZCLPH1,:ZYWLPC1,:ZBZ,:QMGRP,:QMCOD,:ZZTTXT,:POSNR,:FEGRP,:FECOD," &
                      " :ContainerID,:WorkflowstepID,:LossQty,:CreateDate,:CheckEmployeeID,sysDate,0,1,0,:ContainerStepScrapInfoID,:qtyhistorydetailsid," &
                      " :ZXMNO1,:ZXMNO2,:ZXMNO3,:ZXMNO4,:ZXMNO5,:ZXMNO6,:ZXMNO7,:ZXMNO8,:ZGAMNG1,:ZGAMNG2,:ZGAMNG3,:ZGAMNG4,:ZGAMNG5,:ZGAMNG6,:ZGAMNG7,:ZGAMNG8)")
        Dim insertParam(58) As OracleParameter
        insertParam(0) = DatabaseHelper.NewParameter(":QMNUM", para("QMNUM"), OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":MATNR", para("MATNR"), OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":FERTAUFNR", para("FERTAUFNR"), OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":FERTVORNR", para("FERTVORNR"), OracleType.VarChar)
        insertParam(4) = DatabaseHelper.NewParameter(":ZMESGXTXT", para("ZMESGXTXT"), OracleType.VarChar)
        insertParam(5) = DatabaseHelper.NewParameter(":UVORN", para("UVORN"), OracleType.VarChar)
        insertParam(6) = DatabaseHelper.NewParameter(":ZLTXA1", para("ZLTXA1"), OracleType.VarChar)
        insertParam(7) = DatabaseHelper.NewParameter(":ZZFPLX", para("ZZFPLX"), OracleType.VarChar)
        insertParam(8) = DatabaseHelper.NewParameter(":ZBFLJLX", para("ZBFLJLX"), OracleType.VarChar)
        insertParam(9) = DatabaseHelper.NewParameter(":ZZFPPLY", para("ZZFPPLY"), OracleType.VarChar)
        insertParam(10) = DatabaseHelper.NewParameter(":ZFPDBH", para("ZFPDBH"), OracleType.VarChar)
        insertParam(11) = DatabaseHelper.NewParameter(":ZJX", para("ZJX"), OracleType.VarChar)
        insertParam(12) = DatabaseHelper.NewParameter(":ZPCH", para("ZPCH"), OracleType.VarChar)
        insertParam(13) = DatabaseHelper.NewParameter(":CheckEmployeeID", para("CheckEmployeeID"), OracleType.VarChar)
        insertParam(14) = DatabaseHelper.NewParameter(":ZLPH", para("ZLPH"), OracleType.VarChar)
        insertParam(15) = DatabaseHelper.NewParameter(":ZZRDW", para("ZZRDW"), OracleType.VarChar)
        insertParam(16) = DatabaseHelper.NewParameter(":ZZRZ", para("ZZRZ"), OracleType.VarChar)
        insertParam(17) = DatabaseHelper.NewParameter(":ZJYY", para("ZJYY"), OracleType.VarChar)
        insertParam(18) = DatabaseHelper.NewParameter(":ZJYSZR", para("ZJYSZR"), OracleType.VarChar)
        insertParam(19) = DatabaseHelper.NewParameter(":FETXT", para("FETXT"), OracleType.VarChar)
        insertParam(20) = DatabaseHelper.NewParameter(":ZQXTXT", para("ZQXTXT"), OracleType.VarChar)
        insertParam(21) = DatabaseHelper.NewParameter(":EmployeeID", para("EmployeeID"), OracleType.VarChar)
        insertParam(22) = DatabaseHelper.NewParameter(":ZGD", para("ZGD"), OracleType.VarChar)
        insertParam(23) = DatabaseHelper.NewParameter(":ZFPSL", para("ZFPSL"), OracleType.VarChar)
        insertParam(24) = DatabaseHelper.NewParameter(":ZFPZL", para("ZFPZL"), OracleType.VarChar)
        insertParam(25) = DatabaseHelper.NewParameter(":ZDW", para("ZDW"), OracleType.VarChar)
        insertParam(26) = DatabaseHelper.NewParameter(":ZYWLH", para("ZYWLH"), OracleType.VarChar)
        insertParam(27) = DatabaseHelper.NewParameter(":ZCLPH1", para("ZCLPH1"), OracleType.VarChar)        '
        insertParam(28) = DatabaseHelper.NewParameter(":ZYWLPC1", para("ZYWLPC1"), OracleType.VarChar)
        insertParam(29) = DatabaseHelper.NewParameter(":ZBZ", para("ZBZ"), OracleType.VarChar)
        insertParam(30) = DatabaseHelper.NewParameter(":QMGRP", para("QMGRP"), OracleType.VarChar)
        insertParam(31) = DatabaseHelper.NewParameter(":QMCOD", para("QMCOD"), OracleType.VarChar)
        insertParam(32) = DatabaseHelper.NewParameter(":ZZTTXT", para("ZZTTXT"), OracleType.VarChar)
        insertParam(33) = DatabaseHelper.NewParameter(":POSNR", para("POSNR"), OracleType.VarChar)
        insertParam(34) = DatabaseHelper.NewParameter(":FEGRP", para("FEGRP"), OracleType.VarChar)
        insertParam(35) = DatabaseHelper.NewParameter(":FECOD", para("FECOD"), OracleType.VarChar)
        insertParam(36) = DatabaseHelper.NewParameter(":ContainerID", para("ContainerID"), OracleType.VarChar)
        insertParam(37) = DatabaseHelper.NewParameter(":WorkflowstepID", para("WorkflowstepID"), OracleType.VarChar)
        insertParam(38) = DatabaseHelper.NewParameter(":LossQty", para("LossQty"), OracleType.Number)
        insertParam(39) = DatabaseHelper.NewParameter(":CreateDate", para("CreateDate"), OracleType.DateTime)
        insertParam(40) = DatabaseHelper.NewParameter(":ContainerStepScrapInfoID", para("ContainerStepScrapInfoID"), OracleType.VarChar)
        insertParam(41) = DatabaseHelper.NewParameter(":FactoryID", para("FactoryID"), OracleType.VarChar)
        insertParam(42) = DatabaseHelper.NewParameter(":qtyhistorydetailsid", para("QtyhistoryDetailsID"), OracleType.VarChar) 'add YangJiang 20180801
        insertParam(43) = DatabaseHelper.NewParameter(":ZXMNO1", para("ZXMNO1"), OracleType.VarChar)
        insertParam(44) = DatabaseHelper.NewParameter(":ZXMNO2", para("ZXMNO2"), OracleType.VarChar)
        insertParam(45) = DatabaseHelper.NewParameter(":ZXMNO3", para("ZXMNO3"), OracleType.VarChar)
        insertParam(46) = DatabaseHelper.NewParameter(":ZXMNO4", para("ZXMNO4"), OracleType.VarChar)
        insertParam(47) = DatabaseHelper.NewParameter(":ZXMNO5", para("ZXMNO5"), OracleType.VarChar)
        insertParam(48) = DatabaseHelper.NewParameter(":ZXMNO6", para("ZXMNO6"), OracleType.VarChar)
        insertParam(49) = DatabaseHelper.NewParameter(":ZXMNO7", para("ZXMNO7"), OracleType.VarChar)
        insertParam(50) = DatabaseHelper.NewParameter(":ZXMNO8", para("ZXMNO8"), OracleType.VarChar)
        If String.IsNullOrWhiteSpace(para("ZGAMNG1")) Then
            insertParam(51) = DatabaseHelper.NewParameter(":ZGAMNG1", DBNull.Value, OracleType.Number)
        Else
            insertParam(51) = DatabaseHelper.NewParameter(":ZGAMNG1", para("ZGAMNG1"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG2")) Then
            insertParam(52) = DatabaseHelper.NewParameter(":ZGAMNG2", DBNull.Value, OracleType.Number)
        Else
            insertParam(52) = DatabaseHelper.NewParameter(":ZGAMNG2", para("ZGAMNG2"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG3")) Then
            insertParam(53) = DatabaseHelper.NewParameter(":ZGAMNG3", DBNull.Value, OracleType.Number)
        Else
            insertParam(53) = DatabaseHelper.NewParameter(":ZGAMNG3", para("ZGAMNG3"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG4")) Then
            insertParam(54) = DatabaseHelper.NewParameter(":ZGAMNG4", DBNull.Value, OracleType.Number)
        Else
            insertParam(54) = DatabaseHelper.NewParameter(":ZGAMNG4", para("ZGAMNG4"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG5")) Then
            insertParam(55) = DatabaseHelper.NewParameter(":ZGAMNG5", DBNull.Value, OracleType.Number)
        Else
            insertParam(55) = DatabaseHelper.NewParameter(":ZGAMNG5", para("ZGAMNG5"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG6")) Then
            insertParam(56) = DatabaseHelper.NewParameter(":ZGAMNG6", DBNull.Value, OracleType.Number)
        Else
            insertParam(56) = DatabaseHelper.NewParameter(":ZGAMNG6", para("ZGAMNG6"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG7")) Then
            insertParam(57) = DatabaseHelper.NewParameter(":ZGAMNG7", DBNull.Value, OracleType.Number)
        Else
            insertParam(57) = DatabaseHelper.NewParameter(":ZGAMNG7", para("ZGAMNG7"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG8")) Then
            insertParam(58) = DatabaseHelper.NewParameter(":ZGAMNG8", DBNull.Value, OracleType.Number)
        Else
            insertParam(58) = DatabaseHelper.NewParameter(":ZGAMNG8", para("ZGAMNG8"), OracleType.Number)
        End If
        'insertParam(51) = DatabaseHelper.NewParameter(":ZGAMNG1", para("ZGAMNG1"), OracleType.Number)
        'insertParam(52) = DatabaseHelper.NewParameter(":ZGAMNG2", para("ZGAMNG2"), OracleType.Number)
        'insertParam(53) = DatabaseHelper.NewParameter(":ZGAMNG3", para("ZGAMNG3"), OracleType.Number)
        'insertParam(54) = DatabaseHelper.NewParameter(":ZGAMNG4", para("ZGAMNG4"), OracleType.Number)
        'insertParam(55) = DatabaseHelper.NewParameter(":ZGAMNG5", para("ZGAMNG5"), OracleType.Number)
        'insertParam(56) = DatabaseHelper.NewParameter(":ZGAMNG6", para("ZGAMNG6"), OracleType.Number)
        'insertParam(57) = DatabaseHelper.NewParameter(":ZGAMNG7", para("ZGAMNG7"), OracleType.Number)
        'insertParam(58) = DatabaseHelper.NewParameter(":ZGAMNG8", para("ZGAMNG8"), OracleType.Number)
        If DatabaseHelper.ExecuteSql(strsql.ToString, insertParam) > 0 Then
            InsiteScrapData = True
            Exit Function
        End If
        InsiteScrapData = False

    End Function

    ''' <summary>
    ''' 更新废品通知单信息记录表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateScrapData(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSScrapInfoIBusiness.UpdateScrapData
        UpdateScrapData = False

        '装试是手动填写废品单号，其他分厂都是ERP推送，增加逻辑（防止修改数据时将ERP推送的废品单号清空）'add:Wangjh 20190508
        Dim selectSql As String = <string>SELECT fa.factoryname,css.*  FROM containerstepscrapinfo css
                                LEFT JOIN Factory fa ON fa.factoryid=css.factoryid</string>.Value
        selectSql += String.Format(" WHERE css.containerstepscrapinfoid='{0}'", para("ContainerStepScrapInfoID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(selectSql)
        If dt.Rows.Count > 0 And (Not dt.Rows(0)("factoryname").ToString.Contains("装配试车")) Then
            para("QMNUM") = dt.Rows(0)("QMNUM").ToString
            para("ZFPDBH") = dt.Rows(0)("ZFPDBH").ToString
        End If
        '

        Dim strsql As String =
            <string>
                UPDATE containerstepscrapinfo SET ZYWLH=:ZYWLH,ZYWLPC1=:ZYWLPC1,ZFPDBH=:ZFPDBH,QMNUM=:QMNUM,ZZFPLX=:ZZFPLX,ZBFLJLX=:ZBFLJLX,ZFPZL=:ZFPZL,ZFPSL=:ZFPSL,
                ZDW=:ZDW,QMGRP=:QMGRP,QMCOD=:QMCOD,FEGRP=:FEGRP,FECOD=:FECOD,ZGD=:ZGD,ZZRDW=:ZZRDW,ZZFPPLY=:ZZFPPLY,ZJX=:ZJX,ZLPH=:ZLPH,ZZRZ=:ZZRZ,ZJYSZR=:ZJYSZR,
                ZQXTXT=:ZQXTXT,ZBZ=:ZBZ,POSNR=:POSNR,
                ZXMNO1=:ZXMNO1,ZXMNO2=:ZXMNO2,ZXMNO3=:ZXMNO3,ZXMNO4=:ZXMNO4,ZXMNO5=:ZXMNO5,ZXMNO6=:ZXMNO6,ZXMNO7=:ZXMNO7,ZXMNO8=:ZXMNO8,
                ZGAMNG1=:ZGAMNG1,ZGAMNG2=:ZGAMNG2,ZGAMNG3=:ZGAMNG3,ZGAMNG4=:ZGAMNG4,ZGAMNG5=:ZGAMNG5,ZGAMNG6=:ZGAMNG6,ZGAMNG7=:ZGAMNG7,ZGAMNG8=:ZGAMNG8
                WHERE ContainerStepScrapInfoID=:ContainerStepScrapInfoID
            </string>
        Dim updatepara(39) As OracleParameter
        'modify 2017/10/10
        updatepara(0) = DatabaseHelper.NewParameter(":ZYWLH", para("ZYWLH"), OracleType.VarChar)
        updatepara(1) = DatabaseHelper.NewParameter(":ZYWLPC1", para("ZYWLPC1"), OracleType.VarChar)
        updatepara(2) = DatabaseHelper.NewParameter(":ZFPDBH", para("ZFPDBH"), OracleType.VarChar)
        updatepara(3) = DatabaseHelper.NewParameter(":QMNUM", para("QMNUM"), OracleType.VarChar)
        updatepara(4) = DatabaseHelper.NewParameter(":ZZFPLX", para("ZZFPLX"), OracleType.VarChar)
        updatepara(5) = DatabaseHelper.NewParameter(":ZBFLJLX", para("ZBFLJLX"), OracleType.VarChar)
        updatepara(6) = DatabaseHelper.NewParameter(":ZFPZL", para("ZFPZL"), OracleType.VarChar)
        updatepara(7) = DatabaseHelper.NewParameter(":ZFPSL", para("ZFPSL"), OracleType.VarChar)
        updatepara(8) = DatabaseHelper.NewParameter(":ZDW", para("ZDW"), OracleType.VarChar)
        updatepara(9) = DatabaseHelper.NewParameter(":QMGRP", para("QMGRP"), OracleType.VarChar)
        updatepara(10) = DatabaseHelper.NewParameter(":QMCOD", para("QMCOD"), OracleType.VarChar)
        updatepara(11) = DatabaseHelper.NewParameter(":FEGRP", para("FEGRP"), OracleType.VarChar)
        updatepara(12) = DatabaseHelper.NewParameter(":FECOD", para("FECOD"), OracleType.VarChar)
        updatepara(13) = DatabaseHelper.NewParameter(":ZGD", para("ZGD"), OracleType.VarChar)
        updatepara(14) = DatabaseHelper.NewParameter(":ZZRDW", para("ZZRDW"), OracleType.VarChar)
        updatepara(15) = DatabaseHelper.NewParameter(":ZZFPPLY", para("ZZFPPLY"), OracleType.VarChar)
        updatepara(16) = DatabaseHelper.NewParameter(":ZJX", para("ZJX"), OracleType.VarChar)
        updatepara(17) = DatabaseHelper.NewParameter(":ZLPH", para("ZLPH"), OracleType.VarChar)
        updatepara(18) = DatabaseHelper.NewParameter(":ZZRZ", para("ZZRZ"), OracleType.VarChar)
        updatepara(19) = DatabaseHelper.NewParameter(":ZJYSZR", para("ZJYSZR"), OracleType.VarChar)
        updatepara(20) = DatabaseHelper.NewParameter(":ZQXTXT", para("ZQXTXT"), OracleType.VarChar)
        updatepara(21) = DatabaseHelper.NewParameter(":ZBZ", para("ZBZ"), OracleType.VarChar)
        updatepara(22) = DatabaseHelper.NewParameter(":POSNR", para("POSNR"), OracleType.VarChar)
        updatepara(23) = DatabaseHelper.NewParameter(":ZXMNO1", para("ZXMNO1"), OracleType.VarChar)
        updatepara(24) = DatabaseHelper.NewParameter(":ZXMNO2", para("ZXMNO2"), OracleType.VarChar)
        updatepara(25) = DatabaseHelper.NewParameter(":ZXMNO3", para("ZXMNO3"), OracleType.VarChar)
        updatepara(26) = DatabaseHelper.NewParameter(":ZXMNO4", para("ZXMNO4"), OracleType.VarChar)
        updatepara(27) = DatabaseHelper.NewParameter(":ZXMNO5", para("ZXMNO5"), OracleType.VarChar)
        updatepara(28) = DatabaseHelper.NewParameter(":ZXMNO6", para("ZXMNO6"), OracleType.VarChar)
        updatepara(29) = DatabaseHelper.NewParameter(":ZXMNO7", para("ZXMNO7"), OracleType.VarChar)
        updatepara(30) = DatabaseHelper.NewParameter(":ZXMNO8", para("ZXMNO8"), OracleType.VarChar)

        If String.IsNullOrWhiteSpace(para("ZGAMNG1")) Then
            updatepara(31) = DatabaseHelper.NewParameter(":ZGAMNG1", DBNull.Value, OracleType.Number)
        Else
            updatepara(31) = DatabaseHelper.NewParameter(":ZGAMNG1", para("ZGAMNG1"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG2")) Then
            updatepara(32) = DatabaseHelper.NewParameter(":ZGAMNG2", DBNull.Value, OracleType.Number)
        Else
            updatepara(32) = DatabaseHelper.NewParameter(":ZGAMNG2", para("ZGAMNG2"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG3")) Then
            updatepara(33) = DatabaseHelper.NewParameter(":ZGAMNG3", DBNull.Value, OracleType.Number)
        Else
            updatepara(33) = DatabaseHelper.NewParameter(":ZGAMNG3", para("ZGAMNG3"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG4")) Then
            updatepara(34) = DatabaseHelper.NewParameter(":ZGAMNG4", DBNull.Value, OracleType.Number)
        Else
            updatepara(34) = DatabaseHelper.NewParameter(":ZGAMNG4", para("ZGAMNG4"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG5")) Then
            updatepara(35) = DatabaseHelper.NewParameter(":ZGAMNG5", DBNull.Value, OracleType.Number)
        Else
            updatepara(35) = DatabaseHelper.NewParameter(":ZGAMNG5", para("ZGAMNG5"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG6")) Then
            updatepara(36) = DatabaseHelper.NewParameter(":ZGAMNG6", DBNull.Value, OracleType.Number)
        Else
            updatepara(36) = DatabaseHelper.NewParameter(":ZGAMNG6", para("ZGAMNG6"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG7")) Then
            updatepara(37) = DatabaseHelper.NewParameter(":ZGAMNG7", DBNull.Value, OracleType.Number)
        Else
            updatepara(37) = DatabaseHelper.NewParameter(":ZGAMNG7", para("ZGAMNG7"), OracleType.Number)
        End If

        If String.IsNullOrWhiteSpace(para("ZGAMNG8")) Then
            updatepara(38) = DatabaseHelper.NewParameter(":ZGAMNG8", DBNull.Value, OracleType.Number)
        Else
            updatepara(38) = DatabaseHelper.NewParameter(":ZGAMNG8", para("ZGAMNG8"), OracleType.Number)
        End If
       
        updatepara(39) = DatabaseHelper.NewParameter(":ContainerStepScrapInfoID", para("ContainerStepScrapInfoID"), OracleType.VarChar)
        '
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql, updatepara)
        If row > 0 Then
            UpdateScrapData = True
        End If
    End Function

    ''' <summary>
    ''' 根据批次查该批次的型别
    ''' </summary>
    ''' <param name="ContainerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetZJXByContainerID(ByVal ContainerID As String) As System.Data.DataTable Implements IBusiness.uLMSScrapInfoIBusiness.GetZJXByContainerID
        Dim strSQL As New StringBuilder
        strSQL.AppendLine(" SELECT pf.productfamilyname,pf.productfamilyid FROM productfamily pf")
        strSQL.AppendLine(" LEFT JOIN containerproductfamilylist cpf ON cpf.productfamilylistid=pf.productfamilyid")
        strSQL.AppendFormat("  WHERE cpf.containerid='{0}'", ContainerID)
        GetZJXByContainerID = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)
    End Function

    ''' <summary>
    ''' 初始化责任单位
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetZRFactory(ByVal para As Dictionary(Of String, String)) As System.Data.DataTable Implements IBusiness.uLMSScrapInfoIBusiness.GetZRFactory
        Dim strsql As String =
            <string>
               SELECT v.vendorname,v.vendorname||v.description ZZRDW  FROM vendor v
            </string>
        If para.Keys.Contains("FavtoryName") Then
            strsql += " WHERE v.description LIKE '%" & para("FavtoryName") & "%'"
        End If
        GetZRFactory = DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 初始化生产单位
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSCFactory() As System.Data.DataTable Implements IBusiness.uLMSScrapInfoIBusiness.GetSCFactory
        Dim strsql As String =
            <string>
               SELECT f.factoryid,ua.attributevalue || '-' || f.factoryname QMCOD,f.factoryname,ua.attributevalue FROM factory f 
                LEFT JOIN Userattribute ua ON ua.parentid=f.factoryid
                WHERE ua.userattributename='分厂代码'
            </string>
        GetSCFactory = DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取废品单继承信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetLastScrapInfo(ByVal FactoryID As String) As System.Data.DataTable Implements IBusiness.uLMSScrapInfoIBusiness.GetLastScrapInfo
        Dim strSQL = "SELECT csi.zjyszr FROM containerstepscrapinfo csi WHERE csi.checkdate=(SELECT MAX(c.checkdate) FROM containerstepscrapinfo c  WHERE c.factoryid='" & FactoryID & "')"
        GetLastScrapInfo = DatabaseHelper.Query_ReturnDatTable(strSQL)
    End Function

    ''' <summary>
    ''' 初始化编码代码
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetQMCOD() As System.Data.DataTable Implements IBusiness.uLMSScrapInfoIBusiness.GetQMCOD
        Dim strsql As String =
           <string>               
                SELECT f.factoryname,ua.attributevalue FROM factory f 
                LEFT JOIN Userattribute ua ON ua.parentid=f.factoryid
                WHERE ua.userattributename='编码代码'
            </string>
        GetQMCOD = DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 根据批次获取对应机型 add YangJiang 20180517
    ''' </summary>
    ''' <param name="IDs"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductFamily(ByVal IDs() As String) As System.Data.DataTable Implements IBusiness.uLMSScrapInfoIBusiness.GetProductFamily
        Dim strSQL As New StringBuilder
        strSQL.AppendLine("SELECT pf.productfamilyid,pf.productfamilyname FROM productfamily pf WHERE pf.productfamilyid IN (")
        For i = 0 To IDs.Length - 1
            strSQL.AppendLine("'" & IDs(i) & "',")
        Next
        Dim str = strSQL.ToString
        str = str.Substring(0, str.LastIndexOf(","))
        str += ")"
        GetProductFamily = DatabaseHelper.Query_ReturnDatTable(str)
    End Function

    ''' <summary>
    ''' 获取装试未审核废品单信息 add YangJiang 20180612
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetZSScrapInfoUnchecked(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSScrapInfoIBusiness.GetZSScrapInfoUnchecked
        Dim dt As New DataTable
        Dim strsql As New StringBuilder
        'Modify by Yuxj20181010 cem.CreateDate-->ips.CreateDate
        strsql.AppendLine(" SELECT  CASE WHEN cm.containerid IS NULL THEN em.fullname  WHEN cm.containerid IS NOT NULL THEN '' END AS Employee,e.fullname ZJYY, cem.productname MATNR,ips.lossqty LossQty,s.specno UVORN,s.parentspecno FERTVORNR,cem.uom ZDW,")
        strsql.AppendLine(" cem.productbatchno ZPCH,p.materialtrademark ZCLPH1,u.attributevalue ZLPH,s.opdesc ZLTXA1,m.mfgordername FERTAUFNR, c.familylistids ZJX,")
        strsql.AppendLine("c.containerid,wfs.workflowstepid,ips.CreateDate,pb.productname,p.description productdescription,ips.issueerpmaterialscrapinfoid,to_number(nvl(csi.zfpsl,0)) zfpsl,(ips.lossqty-nvl(csi.zfpsl,0)) Qty,")
        strsql.AppendLine("cm.containerid WAIWEI,nvl(wc.workcentername,c.fromworkcentername) ZGD,DECODE(cm.containerid,NULL,e.fullname,eem.fullname) ZBFR,DECODE(cm.containerid,NULL,e.employeeid,eem.employeeid) employeeid")
        strsql.AppendLine(" FROM issueerpmaterialscrapinfo ips")
        strsql.AppendLine(" LEFT JOIN issueerpmaterial ip ON ip.issueerpmaterialid=ips.issueerpmaterialid")
        strsql.AppendLine(" LEFT JOIN COLLECTERPMATERIAL cem ON cem.collecterpmaterialid=ips.collecterpmaterialid")
        strsql.AppendLine("  LEFT JOIN ( SELECT css.qtyhistorydetailsid,SUM(css.zfpsl) zfpsl FROM containerstepscrapinfo css GROUP BY css.qtyhistorydetailsid) csi ON ips.issueerpmaterialscrapinfoid=csi.qtyhistorydetailsid")
        strsql.AppendLine(" LEFT JOIN Workflowstep wfs ON ips.workflowstepid=wfs.workflowstepid")
        strsql.AppendLine(" LEFT JOIN containercommissioning cm ON cm.containerid=ip.containerid")
        strsql.AppendLine(" LEFT JOIN spec s ON s.specbaseid=wfs.specbaseid or s.specid=wfs.specid")
        strsql.AppendLine(" LEFT JOIN containercurrentinfo c ON ip.containerid=c.containerid")
        strsql.AppendLine(" LEFT JOIN containerproductfamilylist cpl ON cpl.containerid=c.containerid")
        strsql.AppendLine(" LEFT JOIN product p ON p.productid=c.productid")
        strsql.AppendLine(" LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid")
        strsql.AppendLine(" LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid")
        strsql.AppendLine(" LEFT JOIN userattribute u ON c.containerid=u.parentid AND u.userattributename='熔炉号母'")
        strsql.AppendLine(" LEFT JOIN employee em ON ips.workerid=em.employeeid")
        strsql.AppendLine(" LEFT JOIN employee e ON ips.checkerid=e.employeeid")
        strsql.AppendLine(" LEFT JOIN sessionvalues sv ON sv.employeeid=NVL(em.employeeid,e.employeeid)")
        'and csw.isuse = 0  add by tianFW 2018年9月29日
        strsql.AppendLine(" LEFT JOIN ContainerStepWorkReport csw ON CSw.Containerid=ip.containerid and csw.isuse = 0 ")
        strsql.AppendLine(" LEFT JOIN workcenter wc ON wc.workcenterid=csw.workcenterid")
        strsql.AppendLine(" LEFT JOIN employee eem ON eem.employeeid=cm.inemployeeid")
        strsql.AppendFormat(" WHERE  (csi.qtyhistorydetailsid IS  NULL OR csi.zfpsl< ips.lossqty) AND sv.factoryid='{0}'", para("FactoryID"))
        strsql.AppendLine(" AND c.containername IS NOT NULL AND m.mfgordername IS NOT NULL ")
        If para.Keys.Contains("ContainerID") Then
            strsql.AppendFormat(" AND c.containerid = '{0}'", para("ContainerID"))
        End If
        If para.Keys.Contains("ProductName") Then
            strsql.AppendFormat(" AND pb.productname LIKE '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("ContainerName") Then
            strsql.AppendFormat(" AND c.containername LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("mfgOrderName") Then
            strsql.AppendFormat(" AND m.mfgordername LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.Keys.Contains("WorkflowStepName") Then
            strsql.AppendFormat(" AND s.parentspecno LIKE '%{0}%'", para("WorkflowStepName"))
        End If
        If para.Keys.Contains("BeginDate") Then
            strsql.AppendFormat(" AND qd.checkdate >TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("BeginDate")))
        End If
        If para.Keys.Contains("EndDate") Then
            strsql.AppendFormat(" AND qd.checkdate <TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("EndDate")))
        End If
        strsql.AppendLine("GROUP BY (em.fullname ,e.fullname , pb.productname ,ips.lossqty ,s.specno ,s.parentspecno ,csi.zfpsl, cem.productbatchno ,p.materialtrademark ,ips.issueerpmaterialscrapinfoid,")
        strsql.AppendLine("u.attributevalue ,s.opdesc ,m.mfgordername , c.containerid ,em.employeeid ,wfs.workflowstepid ,ips.CreateDate ,c.mfgorderid,u.parentid,")
        strsql.AppendLine("cem.productname,p.description,c.familylistids,c.familylistnames,cm.containerid,cem.uom,nvl(wc.workcentername,c.fromworkcentername)")
        strsql.AppendLine(",DECODE(cm.containerid,NULL,e.fullname,eem.fullname),DECODE(cm.containerid,NULL,e.employeeid,eem.employeeid),")
        strsql.AppendLine("CASE WHEN cm.containerid IS NULL THEN em.fullname   WHEN cm.containerid IS NOT NULL THEN '' END),ips.lossqty-nvl(csi.zfpsl,0)")

        strsql.AppendLine(" UNION ALL ")

        strsql.AppendLine(<string>
                                 SELECT DISTINCT e2.fullname Employee,e.fullname ZJYY,zrs.productname MATNR,zrs.lossqty,substr(ws.workflowstepname,0,INSTR(ws.workflowstepname,'-')-1) UVORN  ,'' FERTVORNR,
       '件' ZDW,zrs.batchno ZPCH,p.materialtrademark ZCLPH1,'' ZLPH,substr(ws.workflowstepname,INSTR(ws.workflowstepname,'-')+1) ZLTXA1,zrs.MFGORDERNAME FERTAUFNR,
       '' ZJX,zrs.containerid,zrs.workflowstepid,zrs.createdate,zrs.productname,p.description productdescription,zrs.zsreplacebuyproductscrapinfoid issueerpmaterialscrapinfoid,
      to_number(nvl(csi.zfpsl,0)) zfpsl,(zrs.lossqty-nvl(csi.zfpsl,0)) Qty,zrs.containerid WAIWEI,'' ZGD,e.fullname ZBFR,e.employeeid  FROM ZSREPLACEBUYPRODUCTSCRAPINFO zrs
       LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid=zrs.containerstepworkreportid
       LEFT JOIN employee e2 ON e2.employeeid=csw.workerid
       LEFT JOIN employee e ON e.employeeid=zrs.createemployeeid
       LEFT JOIN workflowstep ws ON ws.workflowstepid=zrs.workflowstepid
       LEFT JOIN product p ON p.productid=zrs.productid
       LEFT JOIN containerstepscrapinfo csi ON csi.qtyhistorydetailsid=zrs.zsreplacebuyproductscrapinfoid
where zrs.lossqty>nvl(csi.zfpsl,0)
                            </string>.Value)
        strsql.AppendFormat(" AND zrs.FACTORYID = '{0}'", para("FactoryID"))
        If para.Keys.Contains("ContainerID") Then
            strsql.AppendFormat(" AND zrs.containerid = '{0}'", para("ContainerID"))
        End If
        If para.Keys.Contains("ProductName") Then
            strsql.AppendFormat(" AND zrs.productname LIKE '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("ContainerName") Then
            strsql.AppendFormat(" AND zrs.ContainerNo LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("mfgOrderName") Then
            strsql.AppendFormat(" AND zrs.mfgordername LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.Keys.Contains("WorkflowStepName") Then
            strsql.AppendFormat(" AND ws.WorkflowStepName LIKE '%{0}%'", para("WorkflowStepName"))
        End If
        If para.Keys.Contains("BeginDate") Then
            strsql.AppendFormat(" AND zrs.Createdate >TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("BeginDate")))
        End If
        If para.Keys.Contains("EndDate") Then
            strsql.AppendFormat(" AND zrs.Createdate <TO_DATE('{0}','YYYY/MM/DD HH24:MI:SS')", Date.Parse(para("EndDate")))
        End If


        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)
        Return result
    End Function

    ''' <summary>
    ''' 获取报废单信息Add By Yuxj20180801
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZSScrapInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSScrapInfoIBusiness.GetZSScrapInfo
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                SELECT  
                 cem.productname MATNR,ips.lossqty LossQty,DECODE(csi.containerid,NULL,'待审核','已审核') Status,
                 cem.productbatchno ZPCH,m.mfgordername FERTAUFNR, ips.CreateDate,getempfullname(ips.workerid) Employee,
                 ep.mfgorderid,ips.issueerpmaterialscrapinfoid
                 FROM issueerpmaterialscrapinfo ips
                 LEFT JOIN issueerpmaterial ip ON ip.issueerpmaterialid=ips.issueerpmaterialid
                 LEFT JOIN COLLECTERPMATERIAL cem ON cem.collecterpmaterialid=ips.collecterpmaterialid
                 LEFT JOIN containerstepscrapinfo csi ON ips.issueerpmaterialscrapinfoid=csi.containerstepscrapinfoid 
                 LEFT JOIN containercurrentinfo c ON ip.containerid=c.containerid
                 LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid
                 LEFT JOIN enginepartplan ep ON ep.mfgorderid=c.mfgorderid           
            </string>
        With sb
            .AppendLine(strsql)
            If para.ContainsKey("TogetherFlag") Then
                If para.ContainsKey("GJ") Then
                    .AppendFormat(" where ep.containerNo='{0}' ", para("TogetherFlag"))
                Else
                    .AppendFormat(" where ip.containerNo='{0}' ", para("TogetherFlag"))
                End If

            End If
        End With
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        dt = dt.DefaultView.ToTable(True, "MATNR", "LossQty", "Status", "ZPCH", "FERTAUFNR", "CreateDate", "Employee", "mfgorderid", "issueerpmaterialscrapinfoid")
        Return dt
    End Function
End Class
